package df.bean.process;

import java.sql.ResultSet;
import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;
import java.util.List;

import df.bean.db.conn.DBConnection;
import df.bean.db.table.BankPaymentMonthly;
import df.bean.db.table.Batch;
import df.bean.db.table.Hospital;
import df.bean.db.table.PaymentMode;
import df.bean.db.table.Status;
import df.bean.obj.util.DialogBox;
import df.bean.obj.util.JDate;
import df.bean.obj.util.Utils;
import df.bean.obj.util.Variables;

public class ProcessBankPaymentMonthly extends Process {
    public String hospitalCode = "";
    public String doctorCode = "";
    static public int runningNoForIT = 0;
    static public int runningNoForGI = 0;
//    static public boolean result = true;
    public String tableName = "";

    
    public ProcessBankPaymentMonthly(DBConnection conn) {
        super(conn);
        this.title = "Bank Payment Monthly Calculator";
        this.tableName = "SUMMARY_MONTHLY";
        this.setDBConnection(conn);
    }
    @Override
    public void finalize() {
        //calculatorFrm = null;
        runningNoForIT = 0;
        runningNoForGI = 0;
    }

    @Override
    public void run() {
/*        if (this.getDBConnection().IsClosed()) {  
            if (!this.getDBConnection().Connect()) { DialogBox.ShowError(" �������ö�Դ��͡Ѻ�ҹ�������� "); return ;}
        }
        
        List values = new ArrayList();
        values.add("---- Start ----");
        values.add(JDate.getDate());
        values.add(JDate.getTime());
        ///this.calculatorFrm.setTableValue(values);
        runningNoForIT = 0;
        runningNoForGI = 0;
        this.getDBConnection().beginTrans();
        super.run();    // call super 
        if (this.calculatorFrm.getLstDoctorGroupCode().getModel().getSize() == this.calculatorFrm.getLstDoctorGroupCode().getSelectedIndex()+1) {
            if (result) {
                this.getDBConnection().commitTrans();
                if (this.getDBConnection().IsOpened()) { this.getDBConnection().Close(); }
                DialogBox.ShowInfo("      ��÷ӧҹ�����z�ó�         ");
            } else {
                this.getDBConnection().rollBackTrans();
                if (this.getDBConnection().IsOpened()) { this.getDBConnection().Close(); }
                DialogBox.ShowError("Error in " + this.tableName + ".Calculate !!!! \nDoctor Code : " + doctorCode + "\nTransactions are rollback.");
            } 
        } else { 
            this.getDBConnection().rollBackTrans();
            if (this.getDBConnection().IsOpened()) { this.getDBConnection().Close(); }
            DialogBox.ShowWarning("��÷ӧҹ�ѧ���ú��hӹǹ      \nTransactions are rollback.");
        } 
        
        this.calculatorFrm.stopProcess();
        
        values.clear();
        values.add("==== Stop ====");
        values.add(JDate.getDate());
        values.add(JDate.getTime());
        this.calculatorFrm.setTableValue(values);
        values = null;
        runningNoForIT = 0;
        runningNoForGI = 0; */
    } 
    
    @Override
    public boolean Calculate(String doctorCode, String hospitalCode) {
        boolean ret = true;
        BankPaymentMonthly bpm=null;
        this.doctorCode = doctorCode;
        this.hospitalCode = hospitalCode;
        Hospital hospital=null;
        Statement stmt = null;
        ResultSet rs = null;
        String sql = " select sum(DR_NET_PAID_AMT) as sumAmt, DOCTOR_CODE, BANK_CODE, BANK_BRANCH_CODE, BANK_ACCOUNT_NO, NAME_THAI " + 
                     " from " + tableName + ", DOCTOR " +
                     " where " + tableName + ".DOCTOR_CODE = DOCTOR.CODE " +
                     " and DOCTOR.PAYMENT_MODE_CODE='" + PaymentMode.PAYMENT_BANK + "'" +
                     " and (STATUS_MODIFY <> '" + Status.STATUS_CALCULATED + "'" + " or STATUS_MODIFY is null)" +
                     " and (BATCH_NO is null or BATCH_NO = '') " +
                     " and " + tableName + ".HOSPITAL_CODE='" + hospitalCode + "'" +
                     " and DOCTOR_CODE='" + doctorCode + "'" +
                     " and YYYY='" + Batch.getYyyy() + "' and MM='" + Batch.getMm() + "'" + 
                     " and DR_NET_PAID_AMT > 0" +
                     " group by DOCTOR_CODE, BANK_CODE, BANK_BRANCH_CODE, BANK_ACCOUNT_NO, NAME_THAI";

        try {
            stmt = this.getDBConnection().getConnection().createStatement();
            rs = stmt.executeQuery(sql);
            hospital = new Hospital(hospitalCode, this.getDBConnection());
            bpm = new BankPaymentMonthly();
            bpm.setDBConnection(this.getDBConnection());

            while (rs.next()) {
                this.doctorCode = rs.getString("DOCTOR_CODE");
                bpm.setFileType("10");
                bpm.setRecordType("2");
                bpm.setBatchNumber("100001");
                bpm.setReceivingBankCode(Utils.replAheadWith("0", rs.getString("BANK_CODE"), 3));
                bpm.setReceivingBranchCode(Utils.replAheadWith("0", rs.getString("BANK_BRANCH_CODE"), 4));
                bpm.setReceivingAccountNo(Utils.replAheadWith("0", rs.getString("BANK_ACCOUNT_NO"), 11).trim());
                bpm.setSendingBankCode(Utils.replAheadWith("0", hospital.getBankCode(), 3));
                bpm.setSendingBranchCode(Utils.replAheadWith("0", hospital.getBankBranchCode(), 4));
                bpm.setSendingAccountNo(Utils.replAheadWith("0", hospital.getAccountNo(), 11));
//                bpm.setEffectiveDate(((BankPaymentMonthlyFrm)this.calculatorFrm).getEffectiveDate());
                bpm.setServiceType(BankPaymentMonthly.SERVICE_TYPE_PAYMENT);
                bpm.setClearingHouseCode(BankPaymentMonthly.CLEARING_HOUSE_CODE);
                bpm.setTransferAmount(Utils.replAheadWith("0", Utils.removeString(".", "" + rs.getDouble("sumAmt")), 12));
                bpm.setReceiverInformation(rs.getString("NAME_THAI"));
                bpm.setSenderInformation(hospital.getDescriptionThai());
                bpm.setSendRef(BankPaymentMonthly.SEND_REF);
                bpm.setVendorID(this.doctorCode);
                bpm.setDateOfOrder(BankPaymentMonthly.DATE_OF_ORDER);
                bpm.setInformationOrder(BankPaymentMonthly.INFORMATION_ORDER);
                bpm.setOtherInformation(BankPaymentMonthly.OTHER_INFORMATION);
                if ( bpm.getReceivingBankCode().equalsIgnoreCase(bpm.getSendingBankCode()) )  {
                    runningNoForIT ++;
                    bpm.setReferenceRunningNo(Utils.replAheadWith("0", ""+runningNoForIT, 6));
                    bpm.setTransType(BankPaymentMonthly.TRANS_TYPE_IT); 
                } else {
                    runningNoForGI ++;
                    bpm.setReferenceRunningNo(Utils.replAheadWith("0", ""+runningNoForGI, 6));
                    bpm.setTransType(BankPaymentMonthly.TRANS_TYPE_GI);
                }
                bpm.setFiller(BankPaymentMonthly.FILTER);
                bpm.setHospitalCode(hospitalCode);
                bpm.setUpdateDate(JDate.getDate());
                bpm.setUpdateTime(JDate.getTime());
                bpm.setUserId(Variables.getUserID());
                bpm.setBatchNo("");
                bpm.setYyyy(Batch.getYyyy());
                bpm.setMm(Batch.getMm());
                
                if (bpm.insert()) {
                    ret = true;
                } else {
                    ret = false;
                    break;
                } 
                
                List values = new ArrayList();
                values.add(bpm.getFileType());
                values.add(bpm.getRecordType());
                values.add(bpm.getBatchNumber());
                values.add(bpm.getReceivingBankCode());
                values.add(bpm.getReceivingBranchCode());
                values.add(bpm.getReceivingAccountNo());
                values.add(bpm.getSendingBankCode());
                values.add(bpm.getSendingBranchCode());
                values.add(bpm.getSendingAccountNo());
                values.add(bpm.getEffectiveDate());
                values.add(bpm.getServiceType());
                values.add(bpm.getClearingHouseCode());
                values.add(bpm.getTransferAmount());
                values.add(bpm.getReceiverInformation());
                values.add(bpm.getSenderInformation());
                values.add(bpm.getSendRef());
                values.add(bpm.getVendorID());
                values.add(bpm.getDateOfOrder());
                values.add(bpm.getInformationOrder());
                values.add(bpm.getReferenceRunningNo());
                values.add(bpm.getTransType());
                values.add(bpm.getFiller());
                values.add(bpm.getOtherInformation());
                
                values.add(bpm.getHospitalCode());
                values.add(bpm.getUpdateDate());
                values.add(bpm.getUpdateTime());
                values.add(bpm.getUserId());
                values.add(bpm.getBatchNo());
//                this.calculatorFrm.setTableValue(values);
                values = null;
            }
            
        } catch (SQLException e) {
            // TODO
            e.printStackTrace();
            ret=false;
            DialogBox.ShowInfo("Error in " + this.tableName + ".Calculate !!!! \nDoctor Code : " + doctorCode + "\n"+ e.getMessage());
        } finally {
               //Clean up resources, close the connection.
                try {
                    if(rs != null) {
                        rs.close();
                        rs = null;
                    }
                    if (stmt != null) {
                        stmt.close();
                        stmt = null;
                    }
                } catch (Exception ignored) { ignored.printStackTrace();   }
        }
        
        if (ret) {
            ret = this.setSummaryMonthlyStatusCalculated();
        }
                                                   
        bpm = null;
        if (result) {  result = ret; }
        return ret;
    } 
    
    // used
    public boolean setSummaryMonthlyStatusCalculated() {
        boolean ret = false;
        String sql = "update " + tableName + " set STATUS_MODIFY='" + Status.STATUS_CALCULATED + "'";
        sql = sql + " where HOSPITAL_CODE='" + this.hospitalCode + "'";
        sql = sql + " and DOCTOR_CODE='" + this.doctorCode + "'";
        sql = sql + " and (BATCH_NO is null or BATCH_NO = '')";
        sql = sql + " and (STATUS_MODIFY <> '" + Status.STATUS_CALCULATED + "' or STATUS_MODIFY is null)";
        if (this.getConn().executeUpdate(sql)>-1) {
            ret = true;
        } 
        return ret;
    }

}
